﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;


namespace DAO
{
    public class SanBayDAO
    {   
        Database db;
        SqlConnection sql;
        DataTable dt;
       
     
       

        public SanBayDAO() 
        {
            db = new Database();
            sql = db.getConnection();
        }
        
        public DataTable LayDSSanBay()
        {
            dt = db.Execute("Select * FROM SANBAY ");
            return dt;
        }
        public void Them(SanBayDTO ma)
        {
                      
            String Ma= ma.Masanbay;
            String Ten= ma.Tensanbay;
            String strSQL = "INSERT INTO SANBAY VALUES('" + Ma + "',N'" + Ten+"')";
            db.ExecuteNonQuery(strSQL);
        }

        public void Xoa(int index)
        {
            string sql = "delete from SANBAY where MASANBAY ='" + dt.Rows[index]["MASANBAY"].ToString()+"'";//phai nho co dao nhay ' ko thi bi bao loi invalid column name
            db.ExecuteNonQuery(sql);
            dt.Rows.RemoveAt(index);
                                        
        }
        public void CapNhat(SanBayDTO sbdto)
        {
            
                SqlCommand updatecmd = new SqlCommand("UpdateSB_Proc", sql);
                updatecmd.Connection = sql;
                updatecmd.CommandType = CommandType.StoredProcedure;

                SqlParameter parMaSB = new SqlParameter("@MaSB", SqlDbType.NVarChar, 3);
                SqlParameter parTenSB = new SqlParameter("@TenSB", SqlDbType.NVarChar, 15);

                parMaSB.Value = sbdto.Masanbay;
                parTenSB.Value = sbdto.Tensanbay;

                updatecmd.Parameters.Add(parMaSB);
                updatecmd.Parameters.Add(parTenSB);

                SqlDataAdapter dataAdapter = new SqlDataAdapter();
                dataAdapter.UpdateCommand = updatecmd;

                sql.Open();
                updatecmd.ExecuteNonQuery();
                sql.Close();
            }
            
        }
    }

